//
//  WBSQLManager.swift
//  Weibo
//
//  Created by xu on 2017/4/2.
//  Copyright © 2017年 xuantong. All rights reserved.
//

import UIKit
import FMDB
private let dbName = "home.db"
class WBSQLManager {

    //全局访问点
    static let shareManager:WBSQLManager = WBSQLManager()
    //c创建队列
    let queue:FMDatabaseQueue
    
      init() {
        //路径
        let path = ((NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).last!) as NSString).appendingPathComponent(dbName)

        
        queue = FMDatabaseQueue(path: path)

        createTable()


    }
    
    
    private func createTable(){

        // 获取文件
        let file = Bundle.main.path(forResource: "db.sql", ofType: nil)!
        
        // 准备sql
        let sql = try! String(contentsOfFile: file)
        // 执行sql
        queue.inDatabase { (db) in
            if db!.executeStatements(sql) {
                print("创建表成功")
            }else{
                print("创建表失败")
            }
        }
        
    }
    // 通过sql语句查询到结果集合 -> 适用于查询为字典数组格式
    func selectDataWith(sql: String) -> [[String: Any]]{
        
        // 定义个可变的空数组
        var tempArray: [[String: Any]] = [[String: Any]]()
        // 执行sql
        WBSQLManager.shareManager.queue.inDatabase { (db) in
        
            // 判断是否为nil
            guard let resultSet =  db?.executeQuery(sql, withArgumentsIn: nil) else{
                return
            }
            // resultSet.columnCount() 代表的每组数据的列数 (属性)
            // 遍历每行
            while resultSet.next(){
                // 定义一个可变的空字典
                var dict: [String: Any] = [String: Any]()
                // 遍历每行对应的每列
                for i in 0..<resultSet.columnCount(){
                    let key = resultSet.columnName(for: i)
                    let value = resultSet.object(forColumnIndex: i)
                    // 赋值
                    dict[key!] = value
                }
                // 保存字典到数组中
                tempArray.append(dict)
            }
        }
        return tempArray
    }
}
